﻿Imports System.Data.SqlClient
Imports Abyx.SVCMS.Library
Imports Abyx.SVCMS.Library.DB

Public Class StkManager
    Inherits ManagerBase

#Region "Constructors"
    Public Sub New(ByVal LMuser As UserType)
        MyBase.New(LMuser)
    End Sub
#End Region

#Region "StockItem"
    Public Function Get_StockItem_FromStockItemId(StockItemId As Guid) As StockItemType
        Dim stockItem As New StockItemType(StockItemId)

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()

            With stockItem
                .Connection = con
                .Load()
            End With

            con.Close()
        End Using

        Return stockItem
    End Function
    Public Function Get_StockItem_All_Col() As StockItemTypeCollection
        Dim spares As New StockItemTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            With spares
                .Connection = con
                .Load("SELECT * FROM [STK_Stock] ORDER BY [ItemCode]")
            End With
            con.Close()
        End Using

        Return spares
    End Function
    Public Function Get_StockItem_Searched(SearchText As String) As DataTable

        Dim spares As New DataTable
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand("SELECT * " & _
                                      "FROM [STK_Stock] " & _
                                      "WHERE " & _
                                      "     (ISNULL([ItemCode],'') + " & _
                                      "     ISNULL([Description],'') + " & _
                                      "     ISNULL([Category],'') +	" & _
                                      "     ISNULL([SubCategory],'')) " & _
                                      " LIKE '%" & SearchText & "%' " & _
                                      "ORDER BY [ItemCode]", con)
            spares = Utility.GetList(cmd)
            con.Close()
        End Using
        Return spares

    End Function
    Public Function Get_StockFieldHistory_Distinct(FieldName As String) As DataTable
        Dim dt As New DataTable

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand("SELECT DISTINCT " & FieldName & " " & _
                                      "FROM [STK_Stock] " & _
                                      "WHERE (NOT " & FieldName & " IS NULL)" & _
                                      "ORDER BY " & FieldName, con)
            dt = Utility.GetList(cmd)
            con.Close()
        End Using

        Return dt
    End Function
    Public Function Get_StockItemCode_Formatted(Optional Codes As Collections.ObjectModel.Collection(Of Integer) = Nothing) As String
        Dim res As String = ""

        'prepare codes
        Dim cString As String = ""
        If Codes IsNot Nothing Then
            For Each code As Integer In Codes
                If cString = "" Then
                    cString = code
                Else
                    cString = cString & "," & code
                End If
            Next
        End If

        'Get item number
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand("SELECT dbo.GetNewItemCode('" & cString & "')", con)
            res = cmd.ExecuteScalar
            con.Close()
        End Using

        Return res
    End Function
    Public Function Update_StockItem_Batch(SparesStockItems As DataTable) As DataTable
        SparesStockItems.Columns.Add(New DataColumn("UpdateStatus", GetType(String)))

        'iterate and do db update
        For Each row As DataRow In SparesStockItems.Rows
            Me.iLMDate = DateTime.Now
            Select Case row.RowState
                Case Is = DataRowState.Added
                    'New record
                    Try
                        Using con As New SqlConnection(Utility.GetConnectionString)
                            con.Open()
                            Dim trans As SqlTransaction = con.BeginTransaction

                            'Insert stock item
                            Dim stockItem As New StockItemType
                            With stockItem
                                .Connection = con
                                .Transaction = trans
                                .Load(row)
                                .LMUserId = Me.iLMuser.UserId
                                .LMDate = Me.iLMDate
                                .Insert()
                            End With

                            'get existing inventory sequence no
                            Dim seq As Integer
                            Dim cmdSeq As New SqlCommand("SELECT ISNULL((MAX(ISNULL(Sequence,0))+1),0) " & _
                                                         "FROM [STK_Inventory] " & _
                                                         "WHERE [StockItemId] = '" & stockItem.StockItemId.ToString & "'")
                            cmdSeq.CommandType = CommandType.Text
                            cmdSeq.Connection = con
                            cmdSeq.Transaction = trans
                            seq = cmdSeq.ExecuteScalar

                            'Update inventory
                            Dim inv As New InventoryType(Guid.NewGuid)
                            With inv
                                .Connection = con
                                .Transaction = trans
                                .StockItemId = stockItem.StockItemId
                                .RefType = STK_InventoryMethodsEnum.Manual_Stock_Entry
                                .RefId = stockItem.StockItemId
                                .PreviousQuantity = stockItem.Quantity
                                .PreviousCostPrice = stockItem.CostPrice
                                .PreviousSellingPrice = stockItem.SellingPrice
                                .PreviousTaxPercentage = stockItem.TaxPercentage
                                .CostCalculationMethod = STK_InventoryCostCalculationMethods.Replace_With_New_Cost
                                .InventoryQuantity = stockItem.Quantity
                                .InventoryCostPrice = stockItem.CostPrice
                                .InventorySellingPrice = stockItem.SellingPrice
                                .InventoryTaxPercentage = stockItem.TaxPercentage
                                .Remarks = "Manual edit of quantity"
                                .AlteredByUser = Me.iLMuser.ToString
                                .AlteredDate = DateTime.Now
                                .LMUserId = Me.iLMuser.UserId
                                .LMDate = Me.iLMDate
                                .Insert()
                            End With

                            trans.Commit()
                            con.Close()
                        End Using
                    Catch ex As SqlException
                        If ex.ErrorCode = -2146232060 Then
                            row.Item("UpdateStatus") = "Error: Duplicate ItemCodes are not allowed"
                        Else
                            row.Item("UpdateStatus") = "Error: " & ex.Message
                        End If
                    Catch ex As Exception
                        row.Item("UpdateStatus") = "Error: " & ex.Message
                    End Try
                Case Is = DataRowState.Modified
                    'Update existing record
                    Try
                        Using con As New SqlConnection(Utility.GetConnectionString)
                            con.Open()
                            Dim trans As SqlTransaction = con.BeginTransaction

                            'get existing qty
                            Dim stockId As Guid = row("StockItemId")
                            Dim exQty As Decimal = 0
                            Using exItem As New StockItemType(stockId)
                                With exItem
                                    .Connection = con
                                    .Transaction = trans
                                    .Load()
                                    exQty = .Quantity
                                End With
                            End Using

                            'get existing inventory sequence no
                            Dim seq As Integer
                            Dim cmdSeq As New SqlCommand("SELECT ISNULL((MAX(ISNULL(Sequence,0))+1),0) " & _
                                                         "FROM [STK_Inventory] " & _
                                                         "WHERE [StockItemId] = '" & stockId.ToString & "'")
                            cmdSeq.CommandType = CommandType.Text
                            cmdSeq.Connection = con
                            cmdSeq.Transaction = trans
                            seq = cmdSeq.ExecuteScalar

                            'Get existing stock item
                            Dim stockItem As New StockItemType(stockId)
                            With stockItem
                                .Connection = con
                                .Transaction = trans
                                .Load()
                                .LMUserId = Me.iLMuser.UserId
                                .LMDate = Me.iLMDate
                                '.Update()
                            End With

                            'Update inventory
                            Dim inv As New InventoryType(Guid.NewGuid)
                            If exQty <> stockItem.Quantity Then
                                With inv
                                    .Connection = con
                                    .Transaction = trans
                                    .StockItemId = stockItem.StockItemId
                                    .RefType = STK_InventoryMethodsEnum.Manual_Stock_Entry
                                    .RefId = stockItem.StockItemId
                                    .PreviousQuantity = stockItem.Quantity
                                    .PreviousCostPrice = stockItem.CostPrice
                                    .PreviousSellingPrice = stockItem.SellingPrice
                                    .PreviousTaxPercentage = stockItem.TaxPercentage
                                    .CostCalculationMethod = STK_InventoryCostCalculationMethods.Replace_With_New_Cost
                                    .InventoryCostPrice = stockItem.CostPrice
                                    .InventorySellingPrice = stockItem.SellingPrice
                                    .InventoryTaxPercentage = stockItem.TaxPercentage
                                    .Remarks = "Manual edit of quantity"
                                    .AlteredByUser = Me.iLMuser.UserName & " (" & Me.iLMuser.AccessLevel.ToString.Replace("_", " ") & ")"
                                    .AlteredDate = DateTime.Now
                                    .Sequence = seq
                                    .LMUserId = Me.iLMuser.UserId
                                    .LMDate = Me.iLMDate
                                End With
                            End If
                            If exQty > stockItem.Quantity Then
                                Dim qty As Decimal = stockItem.Quantity - exQty
                                inv.InventoryQuantity = qty
                                inv.Insert()
                            ElseIf exQty < stockItem.Quantity Then
                                Dim qty As Decimal = stockItem.Quantity - exQty
                                inv.InventoryQuantity = qty
                                inv.Insert()
                            End If

                            'now update new stock item properties
                            stockItem = New StockItemType()
                            With stockItem
                                .Connection = con
                                .Transaction = trans
                                .Load(row)
                                .LMUserId = Me.iLMuser.UserId
                                .LMDate = Me.iLMDate
                                .Update()
                            End With

                            trans.Commit()
                            con.Close()
                        End Using
                    Catch ex As Exception
                        row.Item("UpdateStatus") = "Error: " & ex.Message
                    End Try

                Case Is = DataRowState.Deleted
                    'Delete stock item
                    Try
                        Using con As New SqlConnection(Utility.GetConnectionString)
                            con.Open()
                            Dim trans As SqlTransaction = con.BeginTransaction

                            'Get primary id
                            Dim stockId As Guid = row("StockItemId", DataRowVersion.Original)

                            'check inventory to see if deletion is possible
                            Dim invs As New InventoryTypeCollection
                            With invs
                                .Connection = con
                                .Transaction = trans
                                .Load("SELECT * FROM [STK_Inventory] WHERE [StockItemId]='" & stockId.ToString & "' AND [RefType]<>'Manual Stock Entry'")
                            End With

                            'Delete stock data
                            If invs.Count > 0 Then
                                row.RejectChanges()
                                row.Item("UpdateStatus") = "A valid inventory exists for this item and hence will not be allowed to be deleted"
                            Else
                                Using exItem As New StockItemType(stockId)
                                    With exItem
                                        .Connection = con
                                        .Transaction = trans
                                        .Delete()
                                    End With
                                End Using
                            End If

                            trans.Commit()
                            con.Close()
                        End Using
                    Catch ex As Exception
                        row.Item("UpdateStatus") = "Error: " & ex.Message
                    End Try

            End Select
        Next

        'update stock counts
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE [STK_Stock] " & _
                          "SET [Quantity]=(SELECT (SUM(ISNULL([Quantity],0))) " & _
                          "					FROM [STK_Inventory] " & _
                          "					WHERE [StockItemId] = [STK_Stock].[StockItemId]) " & _
                          "WHERE [Quantity]<>(SELECT (SUM(ISNULL([Quantity],0))) " & _
                          "					 FROM [STK_Inventory] " & _
                          "					 WHERE [StockItemId] = [STK_Stock].[StockItemId])"
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

        'iterate and remove updated rows
        For i As Integer = SparesStockItems.Rows.Count To 0
            Dim row As DataRow = SparesStockItems.Rows(i)
            If row.Item("UpdateStatus") = "" Then
                SparesStockItems.Rows.Remove(row)
            End If
        Next

        Return SparesStockItems
    End Function
#End Region

#Region "Inventory"
    Public Function Get_Inventory_ForBactchId_Col(BatchId As Guid) As InventoryTypeCollection
        Dim inventory As New InventoryTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            inventory.Connection = con
            inventory.Load("SELECT [STK_Inventory].*,[STK_Stock].* " & _
                           "FROM [STK_Inventory] " & _
                           "    INNER JOIN [STK_Stock] ON [STK_Inventory].[StockItemId] = [STK_Stock].[StockItemId] " & _
                           "WHERE [STK_Inventory].[RefId]='" & BatchId.ToString & "' " & _
                           "ORDER BY [STK_Inventory].[AlteredDate] DESC")
            con.Close()
        End Using

        Return inventory
    End Function
    Public Function Get_Inventory_ForStockItemId_Col(StockItemId As Guid) As InventoryTypeCollection
        Dim inventory As New InventoryTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            inventory.Connection = con
            inventory.Load("SELECT [STK_Inventory].*,[STK_Stock].* " & _
                           "FROM [STK_Inventory] " & _
                           "    INNER JOIN [STK_Stock] ON [STK_Inventory].[StockItemId] = [STK_Stock].[StockItemId] " & _
                           "WHERE [STK_Inventory].[StockItemId]='" & StockItemId.ToString & "' " & _
                           "ORDER BY [STK_Inventory].[AlteredDate] DESC")
            con.Close()
        End Using

        Return inventory
    End Function
    Public Function Update_Inventory(inventoryTypeObject As InventoryType) As Boolean

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction
            Me.LMDate = DateTime.Now

            'Load stockobject
            Dim stockitem As New StockItemType(inventoryTypeObject.StockItemId)
            With stockitem
                .Connection = con
                .Transaction = trans
                .Load()
            End With

            'get existing inventory sequence no
            Dim seq As Integer
            Dim cmdSeq As New SqlCommand("SELECT ISNULL((MAX(ISNULL(Sequence,0))+1),0) " & _
                                         "FROM [STK_Inventory] " & _
                                         "WHERE [StockItemId] = '" & inventoryTypeObject.StockItemId.ToString & "'")
            cmdSeq.CommandType = CommandType.Text
            cmdSeq.Connection = con
            cmdSeq.Transaction = trans
            seq = cmdSeq.ExecuteScalar

            'Insert inventory record
            With inventoryTypeObject
                .Connection = con
                .Transaction = trans
                .LMDate = Me.LMDate
                .LMUserId = Me.LMuser.UserId
                .PreviousQuantity = stockitem.Quantity
                .PreviousCostPrice = stockitem.CostPrice
                .PreviousSellingPrice = stockitem.SellingPrice
                .PreviousTaxPercentage = stockitem.TaxPercentage
                .CostCalculationMethod = STK_InventoryCostCalculationMethods.Replace_With_New_Cost
                .AlteredByUser = Me.LMuser.ToString
                .AlteredDate = Me.LMDate
                .Sequence = seq
                .Insert()
            End With

            'update stock counts
            Dim cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE [STK_Stock] " & _
                              "SET [Quantity]=(SELECT (SUM(ISNULL([Quantity],0))) " & _
                              "					FROM [STK_Inventory] " & _
                              "					WHERE [StockItemId] = [STK_Stock].[StockItemId]) " & _
                              "WHERE [Quantity]<>(SELECT (SUM(ISNULL([Quantity],0))) " & _
                              "					 FROM [STK_Inventory] " & _
                              "					 WHERE [StockItemId] = [STK_Stock].[StockItemId])"
            cmd.connection = con
            cmd.transaction = trans
            cmd.ExecuteNonQuery()


            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function
#End Region

#Region "InventoryBatches"
    Public Function Get_InventoryBatches_All_Col() As InventoryBatchTypeCollection
        Dim col As New InventoryBatchTypeCollection

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()

            With col
                .Connection = con
                .Load("SELECT * FROM [STK_InventoryBatch] ORDER BY [BatchType],[GrnReferenceNo]")
            End With

            con.Close()
        End Using

        Return col
    End Function
#Region "GRN"
    Public Function Update_Grn(grn As InventoryBatchType, grnItems As InventoryTypeCollection) As Boolean

        'data validation
        If grn.BatchType <> STK_InventoryMethodsEnum.GRN_Entry Then
            Throw New DataValidationException("Batch type is not GRN")
        End If
        If grn.GrnReferenceNo = "" Then
            Throw New DataValidationException("GRN number cannot be empty")
        End If
        If grnItems.Count < 1 Then
            Throw New DataValidationException("Batch contains no inventory items")
        Else
            Dim found As Boolean = False
            For Each item As InventoryType In grnItems
                If item.InventoryQuantity < 1 Then
                    found = True
                    Exit For
                End If
            Next
            If found Then
                Throw New DataValidationException("Batch contains 0 quantity inventory items")
            End If
        End If

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction
            Me.LMDate = DateTime.Now

            'update batch information
            With grn
                .Connection = con
                .Transaction = trans
                .LMDate = Me.LMDate
                .LMUserId = Me.LMuser.UserId
                .AlteredByUser = Me.LMuser.ToString
                .Insert()
            End With

            'update inventory items
            For Each grnItem As InventoryType In grnItems

                'get existing inventory sequence no
                Dim seq As Integer
                Dim cmdSeq As New SqlCommand("SELECT ISNULL((MAX(ISNULL(Sequence,0))+1),0) " & _
                                             "FROM [STK_Inventory] " & _
                                             "WHERE [StockItemId] = '" & grnItem.StockItemId.ToString & "'")
                cmdSeq.CommandType = CommandType.Text
                cmdSeq.Connection = con
                cmdSeq.Transaction = trans
                seq = cmdSeq.ExecuteScalar

                'Insert inventory record
                With grnItem
                    .Connection = con
                    .Transaction = trans
                    .LMDate = Me.LMDate
                    .LMUserId = Me.LMuser.UserId
                    .RefType = STK_InventoryMethodsEnum.GRN_Entry
                    .RefId = grn.InventoryBatchId
                    .PreviousQuantity = .Quantity
                    .PreviousCostPrice = .CostPrice
                    .PreviousSellingPrice = .SellingPrice
                    .PreviousTaxPercentage = .TaxPercentage
                    .AlteredByUser = Me.LMuser.ToString
                    .AlteredDate = Me.LMDate
                    .Sequence = seq
                    .Remarks = "Modified through GRN " & grn.GrnReferenceNo
                    .Insert()
                End With

            Next

            'update stock counts
            Dim cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE [STK_Stock] " & _
                              "SET [Quantity]=(SELECT (SUM(ISNULL([Quantity],0))) " & _
                              "					FROM [STK_Inventory] " & _
                              "					WHERE [StockItemId] = [STK_Stock].[StockItemId]) " & _
                              "WHERE [Quantity]<>(SELECT (SUM(ISNULL([Quantity],0))) " & _
                              "					 FROM [STK_Inventory] " & _
                              "					 WHERE [StockItemId] = [STK_Stock].[StockItemId])"
            cmd.Connection = con
            cmd.Transaction = trans
            cmd.ExecuteNonQuery()


            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function

#End Region
#End Region

#Region "EquipmentSpareparts"
    Public Function Get_EquipmentSpareparts_All_Col() As EquipmentSparepartsTypeCollection

        Dim catSpares As New EquipmentSparepartsTypeCollection
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            With catSpares
                .Connection = con
                .Load()
            End With
            con.Close()
        End Using
        Return catSpares

    End Function
    Public Function Update_EquipmentSpareparts(ByRef EquipmentSparepartsTypeObject As EquipmentSparepartsType) As Boolean

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            'do insert
            With EquipmentSparepartsTypeObject
                .Connection = con
                .LMUserId = Me.LMuser.UserId
                Me.LMDate = DateTime.Now
                .LMDate = Me.LMDate
                .Insert()
                'incase the stock item base type was not loaded earlier
                If .Description = "" Then
                    .Load()
                End If
            End With
            con.Close()
        End Using

        Return True
    End Function
    Public Function Delete_EquipmentSpareparts(EquipmentSparepartsTypeObject As EquipmentSparepartsType) As Boolean

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            EquipmentSparepartsTypeObject.Connection = con
            EquipmentSparepartsTypeObject.Delete()
            con.Close()
        End Using

        Return True
    End Function
#End Region

End Class
